﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class LocationDBAccess
    {

        /// <summary>
        /// Insert Location
        /// </summary>
        /// <param name="objLocation"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool AddNewLocation(Location objLocation, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {                
               // new SqlParameter("@LocationID", objLocation.LocationID),
                new SqlParameter("@Created", objLocation.Created),
                new SqlParameter("@Updated",objLocation.Updated),
                new SqlParameter("@Isactive", objLocation.Isactive),
                new SqlParameter("@Address", objLocation.Address),
                new SqlParameter("@Descriptions", objLocation.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("AddNewLocation", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Update Location
        /// </summary>
        /// <param name="objLocation"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool UpdateLocation(Location objLocation, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                
                new SqlParameter("@LocationID", objLocation.LocationID),
                new SqlParameter("@Created", objLocation.Created),
                new SqlParameter("@Updated",objLocation.Updated),
                new SqlParameter("@Isactive", objLocation.Isactive),
                new SqlParameter("@Address", objLocation.Address),
                new SqlParameter("@Descriptions", objLocation.Descriptions)
            };

            return SqlDBHelper.ExecuteNonQuery("UpdateLocation", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Delete Location
        /// </summary>
        /// <param name="LocationID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public bool DeleteLocation(int LocationID, String CONNECTION_STRING)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@LocationID", LocationID)
            };

            return SqlDBHelper.ExecuteNonQuery("DeleteLocation", CommandType.StoredProcedure, CONNECTION_STRING, parameters);
        }

        /// <summary>
        /// Get Location Details
        /// </summary>
        /// <param name="LocationID"></param>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public Location GetLocationDetails(string LocationID, String CONNECTION_STRING)
        {
            Location objLocation = null;

            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@LocationID",LocationID),
            };
            //Lets get the list of all Location in a datataable
            using (DataTable table = SqlDBHelper.ExecuteParamerizedSelectCommand("GetLocationDetails", CommandType.StoredProcedure, CONNECTION_STRING, parameters))
            {
                //check if any record exist or not
                if (table.Rows.Count == 1)
                {
                    DataRow row = table.Rows[0];

                    //Lets go ahead and create the list of Location
                    objLocation = new Location();

                    //Now lets populate Location details into the list of Location                                          
                    objLocation.LocationID = Convert.ToInt32(row["LocationID"]);
                    objLocation.Created = row["Created"].ToString();
                    objLocation.Updated = row["Updated"].ToString();
                    objLocation.Isactive = row["Isactive"].ToString(); ; 
                    objLocation.Address = row["Address"].ToString();
                    objLocation.Descriptions = row["Descriptions"].ToString();
                }
            }

            return objLocation;
        }


        /// <summary>
        /// Get Location List
        /// </summary>
        /// <param name="CONNECTION_STRING"></param>
        /// <returns></returns>
        public List<Location> GetLocationList(String CONNECTION_STRING)
        {
            List<Location> listLocation = null;

            //Lets get the list of all Location in a datataable
            using (DataTable table = SqlDBHelper.ExecuteSelectCommand("GetLocationList", CommandType.StoredProcedure, CONNECTION_STRING))
            {
                //check if any record exist or not
                if (table.Rows.Count > 0)
                {
                    //Lets go ahead and create the list of Location
                    listLocation = new List<Location>();

                    //Now lets populate Location details into the list of Location
                    foreach (DataRow row in table.Rows)
                    {
                        Location objLocation = new Location();
                        objLocation.LocationID = Convert.ToInt32(row["LocationID"]);
                        objLocation.Created = row["Created"].ToString();
                        objLocation.Updated = row["Updated"].ToString();
                        objLocation.Isactive = row["Isactive"].ToString();
                        objLocation.Address = row["Address"].ToString();
                        objLocation.Descriptions = row["Descriptions"].ToString();

                        listLocation.Add(objLocation);
                    }
                }
            }

            return listLocation;
        }        
    }
}
